package chapter13.section8;

//程序13-10利用CachedRowSet对象更新数据

import javax.sql.rowset.CachedRowSet;
import javax.sql.rowset.RowSetFactory;
import javax.sql.rowset.RowSetProvider;
import javax.sql.rowset.spi.SyncProviderException;
import javax.sql.rowset.spi.SyncResolver;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.Statement;

public class CachedRowSetConflictDemo {
    public static void main(String[] args) throws SQLException {
        String url = "jdbc:mysql://localhost:3306/bank?user=root&password=123456";
        Connection con = DriverManager.getConnection(url);

        RowSetFactory aFactory = RowSetProvider.newFactory();
        CachedRowSet crs = aFactory.createCachedRowSet();
        // 下面两条语句是获得一条准备修改的记录填充crs
        crs.setCommand("select id,name,balance from tb_account where id='26a452b8- 18eb-4704-8f33-aae70b69e61c'");
        crs.execute(con);
        // 下面两条语句是直接修改对应的数据库记录中name字段的值，
//造成数据库中记录和crs中的数据不一致，形成冲突
        Statement stmt = con.createStatement();
        stmt.executeUpdate("update tb_account set name='测试' where id='26a452b8- 18eb-4704-8f33-aae70b69e61c'");
        // 定义用第1列的数据作为crs中每一行数据的唯一值，类似于关键字的作用
        int[] keys = {1};
        crs.setKeyColumns(keys);
        // 下面的语句更新crs中的第一行数据中的name字段的值
        crs.first();
        crs.updateString("name", "丁莉");
        crs.updateRow();
        con.setAutoCommit(false); // 关闭自动提交模式
        try {
            crs.acceptChanges(con); // 提交crs的更新，检测冲突
        } catch (SyncProviderException spe) {
            SyncResolver resolver = spe.getSyncResolver();
            // 冲突的处理
            Object crsValue; // crs离线数据修改的值
            Object resolverValue; // SyncResolver对象中保存是数据库中已被修改的值
            Object resolvedValue; // 希望最终保存的值
            while (resolver.nextConflict()) {
                if (resolver.getStatus() == SyncResolver.UPDATE_ROW_CONFLICT) {
                    int row = resolver.getRow();
                    crs.absolute(row);
                    int colCount = crs.getMetaData().getColumnCount();
                    for (int j = 1; j <= colCount; j++) {
                        if (resolver.getConflictValue(j) != null) {
                            crsValue = crs.getObject(j);
                            resolverValue = resolver.getConflictValue(j);
                            resolvedValue = crsValue;
                            resolver.setResolvedValue(j, resolvedValue);
                        }
                    }
                }
            }
        } finally {
            if (crs != null) {
                crs.close();
            }
            con.setAutoCommit(true);
        }
    }
}